'''
Created on 2010-11-23

@author: root
'''
from base import *
import MySQLdb
import sys

class mysql(base):
	'''
	mysql database
	'''

	def __init__(self, host="localhost", user="root", pwd="", dbname="mysql", port=3306, charset='utf8'):
		self.host=host
		self.port=port
		self.user=user
		self.pwd=pwd
		self.dbname=dbname
		self.charset=charset
		self.conn=None
		
	def connect(self):
		if self.conn: return
		try:
			self.conn=MySQLdb.connect(	host = self.host,
									user = self.user,
									passwd = self.pwd,
									db = self.dbname)
			
			self.use_charset(self.charset)
			self.use_db(self.dbname)
		except MySQLdb.Error, e:
			print "MySQL Error %d: %s" % (e.args[0], e.args[1])
			sys.exit(1)
		
		
	def close(self):
		if self.conn:
			self.conn.commit()
			self.conn.close()
			self.conn=None
	
	def get_version(self):
		cursor = self.conn.cursor()
		cursor.execute("SELECT VERSION()")
		row = cursor.fetchone()
		v="server version:"+ row[0]
		cursor.close ()
		return v
	
	def run(self, sql):
		cursor=self.conn.cursor()
		cursor.execute(sql)
		rc=cursor.rowcount
		cursor.close ()
		return rc
	
	def use_charset(self, charset):
		self.run("SET NAMES '"+charset+"'");
		
	def use_db(self, dbname):
		if dbname!=self.dbname:
			self.dbname=dbname
			self.run("use "+self.dbname)
	
	def drop_db(self, dbname):
		self.run("drop database "+dbname)
		
	def insert(self, table, cols, vals):
		sql="insert into %s %s values %s" % (table, cols, vals)
		return self.run(sql)
		
	def update(self, table, set, where):
		sql="update %s set %s where %s" % (table, set, where)
		return self.run(sql)
	
	def delete(self, table, where):
		sql="delete from %s where %s" % (table, where)
		return self.run(sql)
	
	def select(self, cols, table, where):
		sql="select %s from %s where %s" % (cols, table, where)
		return self.all(sql)
	
	def drop(self, table):
		self.run("drop table "+table)
	
	def table(self, table):
		return self.all("select * from "+table)
	
	def count(self, sql):
		return self.run(sql)
		
	
	def first(self, sql):
		'''return the first col value of the first row
		'''
		cursor=self.conn.cursor()
		cursor.execute(sql)
		row=cursor.fetchone()
		cursor.close()
		return row[0]
	
	def one(self, sql):
		'''return only one row from record set, accessed by col name
		'''
		cursor=self.conn.cursor(MySQLdb.cursors.DictCursor)
		cursor.execute(sql)
		row=cursor.fetchone()
		cursor.close()
		return row
		
	def all(self, sql):
		'''return all record, accessed by col name
		'''
		cursor=self.conn.cursor(MySQLdb.cursors.DictCursor)
		cursor.execute(sql)
		rows=cursor.fetchall()
		cursor.close ()
		return rows
	
	